/*
 * Reference: Web Apps code provided by Prof Charlie Garrod
 * can be used for educational purposes
 * */
package messagepasser;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class UserInfoDAOSQL {
	private List<Connection> connectionPool = new ArrayList<Connection>();

	private String jdbcDriver;
	private String jdbcURL;
	private String tableName;

	public UserInfoDAOSQL(String jdbcDriver, String jdbcURL, String tableName) {
		this.jdbcDriver = jdbcDriver;
		this.jdbcURL    = jdbcURL;
		this.tableName  = tableName;

		createTable();
	}

	private synchronized Connection getConnection() {
		if (connectionPool.size() > 0) {
			return connectionPool.remove(connectionPool.size()-1);
		}

		try {
			Class.forName(jdbcDriver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new AssertionError(e);
		}

		try {
			return DriverManager.getConnection(jdbcURL);
		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}

	private synchronized void releaseConnection(Connection con) {
		connectionPool.add(con);
	}

	private void createTable() {
		Connection con = getConnection();
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(
					"create table "+tableName+
					" (username varchar(20) NOT NULL," +
					" updates varchar(30)," +
					" id MEDIUMINT auto_increment, primary key(id)," +
			" foreign key(username) references aj1_profile(username))");
			stmt.close();
			releaseConnection(con);
		} catch (SQLException e) {
			try { con.close(); } catch (SQLException e2) { /* ignore */ }
			//System.out.println("Exception creating table: "+e);
			//System.out.println("...assume it already exists");
		}
	}

	public int insertUser(String username, String fname, String lname, String pwd){
		try {

			Connection con = getConnection();
			con.setAutoCommit(false);
			int num=0;
			int id = 0;
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select count(username) as cnt from userprofile where username = \'"+username+"\'");
			while (rs.next()) {
				num = (rs.getInt("cnt"));
			}

			if(num == 0)
			{
				rs = stmt.executeQuery("select max(userid) as id from userprofile");
				while (rs.next()) {
					id = (rs.getInt("id"));
				}
				PreparedStatement pstmt = con.prepareStatement("insert into userprofile(userid,username,password,fname,lname) values (?,?,?,?,?)");
				pstmt.setInt(1, id+1);
				pstmt.setString(2, username);
				pstmt.setString(3, pwd);
				pstmt.setString(4, fname);
				pstmt.setString(5, lname);
				pstmt.executeUpdate();
				con.commit();
				con.setAutoCommit(true);
				con.setAutoCommit(false);
				pstmt = con.prepareStatement("insert into userstatus(userid,username) values (?,?)");
				pstmt.setInt(1, id+1);
				pstmt.setString(2, username);
				pstmt.executeUpdate();
				con.commit();
				con.setAutoCommit(true);
				rs.close();
				stmt.close();
				releaseConnection(con);
				return(id+1);
			}
			else
				return 0;
			

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}


	//After Login to get all the other users information

	public synchronized UserInfo[] getuserinfo(int ownerid) {
		try {
			Connection con = getConnection();

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from userstatus where userid != "+ownerid);

			List <UserInfo> users = new ArrayList <UserInfo>();
			while (rs.next()) {
				System.out.println (rs.getInt("userId"));
				users.add( new UserInfo(rs.getInt("userId"), rs.getString("username"), rs.getBoolean("isonline"), rs.getString("ip"),rs.getInt("port")));
			}

			rs.close();
			stmt.close();
			releaseConnection(con);

			return users.toArray(new UserInfo[users.size()]);

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}
	
	//After Login to get all the other users information

	public synchronized UserInfo getuser(int ownerid) {
		try {
			Connection con = getConnection();

			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from userstatus where userid = "+ownerid);

			UserInfo user = null;
			while (rs.next()) {
				System.out.println (rs.getInt("userId"));
				user = new UserInfo(rs.getInt("userId"), rs.getString("username"), rs.getBoolean("isonline"), rs.getString("ip"),rs.getInt("port"));
			}

			rs.close();
			stmt.close();
			releaseConnection(con);

			return user;

		} catch (SQLException e) {
			throw new AssertionError(e);
		}
	}



}
